from openpyxl import load_workbook
import datetime as dt
from tqdm import tqdm
from tools_pmc import (fill_blue, fill_yellow, fill_orange,
                       font_white,
                       lj_tyl, lj_zyl, today_str1, today_str2
                       )

# 手动汇总，数据透视！！！！

wbkcmxfx = load_workbook(
    "//192.168.70.101/19计划管理部/01.计划/5，物控管理/2，呆滞料管理/呆滞数据整理" + today_str1 + "/file/库存明细账" + today_str1 + "-处理后.xlsx")
wskcmx_clh = wbkcmxfx.active
wbkcmxfx.create_sheet("明细矩阵", 1)
wskcmx_jz = wbkcmxfx.worksheets[1]

datestart = dt.datetime.strptime('2021-07-31', "%Y-%m-%d").date()
dateend = dt.datetime.strptime(today_str2, "%Y-%m-%d").date()
Days = (dateend - datestart).days

for i in range(0, Days + 1):
    wskcmx_jz.cell(1, i + 5).value = (datestart + dt.timedelta(days=i)).strftime('%Y-%m-%d')

print("填入出入库数据：")
x = 5
for i in tqdm(range(2, wskcmx_clh.max_row + 1)):
    if str(wskcmx_clh.cell(i, 1).value) != wskcmx_jz.cell(x - 4, 1).value:
        x += 4
        wskcmx_jz.cell(x - 4, 1).value = str(wskcmx_clh.cell(i, 1).value)
        wskcmx_jz.cell(x - 4, 4).value = "入库数量"
        wskcmx_jz.cell(x - 3, 4).value = "出库数量"
        wskcmx_jz.cell(x - 2, 4).value = "累计入库"
        wskcmx_jz.cell(x - 1, 4).value = "累计出库"
        for j in range(5, Days + 5):
            if wskcmx_jz.cell(1, j).value == wskcmx_clh.cell(i, 2).value:
                wskcmx_jz.cell(x - 4, j).value = wskcmx_clh.cell(i, 3).value
                wskcmx_jz.cell(x - 3, j).value = wskcmx_clh.cell(i, 4).value
                break
    else:
        for j in range(5, Days + 5):
            if wskcmx_jz.cell(1, j).value == wskcmx_clh.cell(i, 2).value:
                wskcmx_jz.cell(x - 4, j).value = wskcmx_clh.cell(i, 3).value
                wskcmx_jz.cell(x - 3, j).value = wskcmx_clh.cell(i, 4).value
                break
print("修补空值数据：")
for i in tqdm(range(5, x - 3, 4)):
    for j in range(5, Days + 5):
        if wskcmx_jz.cell(i, j).value is None:
            wskcmx_jz.cell(i, j).value = 0
        if wskcmx_jz.cell(i + 1, j).value is None:
            wskcmx_jz.cell(i + 1, j).value = 0
print("计算累计出入库数据:")
for i in tqdm(range(5, x - 3, 4)):
    wskcmx_jz.cell(i + 2, 5).value = wskcmx_jz.cell(i, 5).value
    wskcmx_jz.cell(i + 3, 5).value = wskcmx_jz.cell(i + 1, 5).value
    for j in range(6, Days + 5):
        wskcmx_jz.cell(i + 2, j).value = wskcmx_jz.cell(i + 2, j - 1).value + wskcmx_jz.cell(i,
                                                                                             j).value
        wskcmx_jz.cell(i + 3, j).value = wskcmx_jz.cell(i + 3, j - 1).value + wskcmx_jz.cell(
            i + 1, j).value
wskcmx_jz.delete_rows(2, 3)
wbkcmxfx.save(
    "//192.168.70.101/19计划管理部/01.计划/5，物控管理/2，呆滞料管理/呆滞数据整理" + today_str1 + "/file/库存明细账" + today_str1 + "-matrix.xlsx")
